package com.peenongsaban.freelance.service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Scanner;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PostJobBean;
import com.peenongsaban.freelance.dao.ListJobDAO;
import com.peenongsaban.freelance.dao.impl.ListJobDAOImpl;
import com.peenongsaban.freelance.meta.ListPostJobViewMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.model.ListJobPageModel;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;
import com.peenongsaban.freelance.utils.StringUtils;

public class ListJobService {

	public ListJobPageModel listJobByCondition(ListJobPageModel model) {
		String table = ListPostJobViewMeta.VIEW_CONDITON;
		ListJobPageModel newModel = new ListJobPageModel();
		ArrayList<String> sqlCondition = new ArrayList<String>();

		// Keyword
		String keyword = model.getSearchForm().getKeyword();
		if (!StringUtils.isEmptyOrNull(keyword)) {
			Scanner keywordScanner = new Scanner(keyword);
			StringBuffer keywordBuf = new StringBuffer();
			while (keywordScanner.hasNext()) {
				keywordBuf.append(ListPostJobViewMeta.COL_JOB_NAME + " LIKE '%"
						+ keywordScanner.next() + "%' OR ");
			}
			if (keywordBuf.length() != 0) {
				keywordBuf.replace(keywordBuf.length() - 4,
						keywordBuf.length(), "");
			}
			String kwStr = keywordBuf.toString();
			sqlCondition.add(kwStr);
			newModel.getSearchForm().setKeyword(keyword);
		}
		// Aptitude

		// Aptitude Type
		String typeId = model.getSearchForm().getAptitudeType();
		if (!StringUtils.isEmptyOrNull(typeId)) {
			sqlCondition.add(ListPostJobViewMeta.COL_APTITUDE_TYPE_ID + " = "
					+ typeId);
			table = ListPostJobViewMeta.VIEW_CATEGORYTYPE;
			newModel.getSearchForm().setAptitudeType(typeId);
		}

		// Price
		String price = model.getSearchForm().getPrice();
		String strPrice = null;
		if (!StringUtils.isEmptyOrNull(price)) {
			if (price.equals("1")) {
				strPrice = "<= 1000";
			} else if (price.equals("2")) {
				strPrice = "<= 5000";
			} else if (price.equals("3")) {
				strPrice = "<= 10000";
			} else if (price.equals("4")) {
				strPrice = "<= 20000";
			} else if (price.equals("5")) {
				strPrice = "<=50000";
			} else if (price.equals("6")) {
				strPrice = "> 50000";
			}
			newModel.getSearchForm().setPrice(price);
		} else {
			strPrice = ">= 0";
		}

		// Format Time
		String formatTime = model.getSearchForm().getFormatTime();
		if (!StringUtils.isEmptyOrNull(model.getSearchForm().getFormatTime())) {
			String[] fomatTime = formatTime.split(",");

			for (int i = 0; i < fomatTime.length; i++) {
				if (fomatTime[i].trim().equals("2")) {
					fomatTime[i] = "'" + ConstantUtils.FORMAT_TIME_HOUR + "'";
					sqlCondition.add(ListPostJobViewMeta.COL_FIX_PRICE
							+ strPrice);
				} else if (fomatTime[i].trim().equals("3")) {
					fomatTime[i] = "'" + ConstantUtils.FORMAT_TIME_DAY + "'";
					sqlCondition.add(ListPostJobViewMeta.COL_FIX_PRICE
							+ strPrice);
				} else if (fomatTime[i].trim().equals("4")) {
					fomatTime[i] = "'" + ConstantUtils.FORMAT_TIME_MONTH + "'";
					sqlCondition.add(ListPostJobViewMeta.COL_FIX_PRICE
							+ strPrice);
				} else if (fomatTime[i].trim().equals("5")) {
					fomatTime[i] = "'" + ConstantUtils.FORMAT_TIME_PACKAGE
							+ "'";
					sqlCondition.add(ListPostJobViewMeta.COL_FIX_PRICE
							+ strPrice);
				} else if (fomatTime[i].trim().equals("6")) {
					fomatTime[i] = "'" + ConstantUtils.FORMAT_TIME_BID + "'";
					sqlCondition.add(ListPostJobViewMeta.COL_MAX_PRICE
							+ strPrice);
				} else {
					sqlCondition.add(ListPostJobViewMeta.COL_FIX_PRICE
							+ strPrice + " OR "
							+ ListPostJobViewMeta.COL_MAX_PRICE + strPrice);
				}
			}
			String f = Arrays.toString(fomatTime);
			sqlCondition.add(ListPostJobViewMeta.COL_FORMAT_TIME + " IN ("
					+ f.substring(1, f.length() - 1) + ") ");
			newModel.getSearchForm().setFormatTime(formatTime);
		}

		// Kind Job
		if (model.getSearchForm().getKindJob() != "") {
		}

		// Trust Level
		String trustLevel = model.getSearchForm().getTrust();
		if (!StringUtils.isEmptyOrNull(trustLevel)) {
			sqlCondition.add(ListPostJobViewMeta.COL_TRUST_LEVEL + " = "
					+ trustLevel);
			newModel.getSearchForm().setTrust(trustLevel);
		}
		// Work Area
		String workArea = model.getSearchForm().getWorkArea();
		if (!StringUtils.isEmptyOrNull(workArea)) {
			sqlCondition.add(ListPostJobViewMeta.COL_WORK_AREA + " = '"
					+ workArea + "'");
			newModel.getSearchForm().setWorkArea(workArea);
		}

		// Start and End of job
		try {
			String start = model.getSearchForm().getStartDateOfJob();
			String end = model.getSearchForm().getEndDateOfJob();
			if (!StringUtils.isEmptyOrNull(start)
					&& !StringUtils.isEmptyOrNull(end)) {
				sqlCondition.add(ListPostJobViewMeta.COL_START_DATE_OF_JOB
						+ " BETWEEN '" + start + "%' AND '" + end + "%' AND "
						+ ListPostJobViewMeta.COL_END_DATE_OF_JOB
						+ " BETWEEN '" + start + "%' AND '" + end + "%'");
				newModel.getSearchForm().setStartDateOfJob(start);
				newModel.getSearchForm().setEndDateOfJob(end);
			} else if (!StringUtils.isEmptyOrNull(start)
					&& StringUtils.isEmptyOrNull(end)) {
				sqlCondition.add(ListPostJobViewMeta.COL_START_DATE_OF_JOB
						+ " BETWEEN '" + start + " 00:00:00' AND '" + start
						+ " 23:59:59'");
				newModel.getSearchForm().setStartDateOfJob(start);
			} else if (StringUtils.isEmptyOrNull(start)
					&& !StringUtils.isEmptyOrNull(end)) {
				sqlCondition.add(ListPostJobViewMeta.COL_END_DATE_OF_JOB
						+ " BETWEEN '" + end + "  00:00:00' AND '" + end
						+ " 23:59:59'");
				newModel.getSearchForm().setEndDateOfJob(end);
			}
		} catch (Exception ex) {
			ExceptionUtil.messageException(new Throwable(), ex);
		}

		//Post job is open only
		sqlCondition.add(PostJobMeta.COL_STATUS_OF_POST+" = "+"'O' ");
		
		String sql = "";
		int pageNo = model.getPageCurrentNo();
		if (sqlCondition.size() != 0) {
			sql = " WHERE " + sql;
		}

		// Condition SQL command
		for (int i = 0; i < sqlCondition.size(); i++) {
			sql = sql + sqlCondition.get(i);
			if ((i + 1) != sqlCondition.size()) {
				sql = sql + " AND ";
			}
		}
		
		

		String orderBy = model.getSearchForm().getOrderBy();
		String order = " ORDER BY ";
		if(!StringUtils.isEmptyOrNull(orderBy)){
			if(orderBy.equals("1")){
				order += ListPostJobViewMeta.COL_POST_DATE+" DESC";
			}else if(orderBy.equals("2")){
				order += ListPostJobViewMeta.COL_AMOUNT_USER_REQUEST_JOB+" DESC";
			}else if(orderBy.equals("3")){
				order += ListPostJobViewMeta.COL_FIX_PRICE+" DESC";
			}else if(orderBy.equals("4")){
				order += ListPostJobViewMeta.COL_TRUST_LEVEL+" DESC";
			}else if(orderBy.equals("5")){
				order += ListPostJobViewMeta.COL_START_DATE_OF_JOB+" DESC";
			}	
			newModel.getSearchForm().setOrderBy(orderBy);
			sql += order;
		}else{
			order += ListPostJobViewMeta.COL_POST_DATE+" DESC";
			sql += order;
		}
		
		
		// SQL count data size
		String sqlSizeData = sql;

		// Condition SQL command for paging
		int itemList = ConstantUtils.LIMIT_PAGING_LIST_JOB;
		if (pageNo == 0) {
			sql = sql + " LIMIT 0," + itemList;
		} else {
			sql = sql + " LIMIT " + ((pageNo - 1) * itemList) + "," + itemList;
		}

		System.out.println(sql);

		// Select list job for show in page
		Vector<PostJobBean> vector = new Vector<PostJobBean>();
		ListJobDAO dao = new ListJobDAOImpl();
		vector = dao.listPostJobByCondition(table,sql);
		newModel.setListPostJob(vector);

		// Display paging
		int size = dao.countTotalJob(table,sqlSizeData);
		int totalPage = (size / itemList);
		if (size % itemList != 0) {
			totalPage++;
		}
		if (pageNo == 0) {
			newModel.setPageCurrentNo(1);
		} else {
			newModel.setPageCurrentNo(pageNo);
		}

		newModel.setListFirstRowNo(((newModel.getPageCurrentNo() - 1) * itemList) + 1);
		newModel.setListLastRowNo(newModel.getPageCurrentNo() * itemList);
		newModel.setTotalPage(totalPage);
		newModel.setListTotalRow(size);

		Vector<AptitudeTypeBean> listCategoryJob = listCategoryJob();
		newModel.setListCategoryJob(listCategoryJob);

		Vector<PostJobBean> listLastJob = listLastPostJob();
		newModel.setListLastJob(listLastJob);

		return newModel;
	}

	public Vector<AptitudeTypeBean> listCategoryJob() {
		Vector<AptitudeTypeBean> list = new Vector<AptitudeTypeBean>();
		ListJobDAOImpl dao = new ListJobDAOImpl();
		list = dao.listCategoryJob();
		return list;

	}

	public Vector<PostJobBean> listLastPostJob() {
		Vector<PostJobBean> list = new Vector<PostJobBean>();
		ListJobDAOImpl dao = new ListJobDAOImpl();
		list = dao.listLastPostJob();
		return list;
	}

}
